Rowid Range

All Update, Delete, and most Insert statements must use rollback segments. If your DML processes so many rows that it cannot be performed in a single transaction, then you must run the change incrementally with regular commits.

One method of doing this is to use PL/SQL to process the table row by row. For INSERTs, this is not so bad, but for UPDATEs and DELETEs, it means indexed access on each row to be updated or deleted.

A better way is to process the table in chunks. If the table is partitioned and each partition can be processed in a single transaction, then it is fairly easy to construnct a PL/SQL loop around the DML statement to process one partition at a time.

Non-partitioned tables and partitions too big to process within a single transaction are more difficult. In the same way that partitions break a table into manageable chunks, extents break a partition (or non-partitioned table) into chunks. Run the following SQL:

SELECT *
FROM   dba_extents
WHERE  segment_name = 'MY_TABLE'

As your table grows, Oracle allocates new extents in which to store the new rows. Looking at the columns of dba_extents, we can reconstruct the rowids of the first and last row in every extent. Then it is simply a matter of:

UPDATE my_table
SET   ....
WHERE rowid BETWEEN :low_rowid AND :high_rowid;

or,

INSERT
INTO   my_new_table
SELECT ...
FROM   my_table
WHERE rowid BETWEEN :low_rowid AND :high_rowid;

Oracle can use the low and high rowid of each extent to go straight to the rows you want to SELECT for INSERT, UPDATE, or DELETE. See your DBA about using dba_extents to reconstruct rowids.

Warning: If your DBA uses Dictionary Managed Tablespaces, then it is likely that some extents will be too big to process in a single transaction. With Locally Managed Tablespace it is more likely that the extents will be small enough to process with Rowid Range.